const db = require("../../lib/conn_oracle")

exports.getFasRecordCheckInfo = async (req, res) => {
    // console.log(req.body)
    let { form } = req.body
    let { begindate, enddate, line, stations, specialties, subsys, noexists, description } = form
    let resDatas
    let msg
    let resStatus
    let checkDatas = []
    checkDatas.push(line)
    checkDatas.push(begindate)
    checkDatas.push(enddate)    
    //站点条件
    let stationsWhere
    if (stations && stations.length > 1) {
        stationsWhere = `and x.location in (`
        for (let i = 0; i < stations.length; i++)
            stationsWhere += (i > 0) ? ", :" + i : ":" + i
        stationsWhere += ")"
        checkDatas.push(...stations)
    } else if (stations && stations.length === 1) {
        stationsWhere = `and x.location = :stations`
        checkDatas.push(...stations)
    } else { stationsWhere = "" }
    //专业条件
    let specialtiesWhere
    if (specialties && specialties.length > 1) {
        specialtiesWhere = `and x.cust_specnum in (`
        for (let i = 0; i < specialties.length; i++)
            specialtiesWhere += (i > 0) ? ", :" + i : ":" + i
        specialtiesWhere += ")"
        checkDatas.push(...specialties)
    } else if (specialties && specialties.length === 1) {
        specialtiesWhere = `and x.cust_specnum = :specialties`
        checkDatas.push(...specialties)
    } else { specialtiesWhere = "" }
    //子系统条件
    let subsysWhere
    if (subsys && subsys.length > 1) {
        subsysWhere = `and x.cust_subsys in (`
        for (let i = 0; i < subsys.length; i++)
            subsysWhere += (i > 0) ? ", :" + i : ":" + i
        subsysWhere += ")"
        checkDatas.push(...subsys)
    } else if (subsys && subsys.length === 1) {
        subsysWhere = `and x.cust_subsys = :subsys`
        checkDatas.push(...subsys)
    } else { subsysWhere = "" }
    //设备描述
    let descLike
    let descWhere
    if (description.length > 0) {
        descLike = `%${description}%`
        descWhere = `and x.description like :descLike`
        checkDatas.push(descLike)
    } else {
        descLike = ""
        descWhere = ""
    }
    //是否无现场设备记录
    let noexistsWhere
    let colName
    let tableJoin
    let eventtimeOrder
    if (noexists === true) {
        noexistsWhere = line === 'L03' ? `
                                        and not exists (
                                                select 1 from C##WXW.GCC_LIST g
                                                where xf.sbdesc = g.sbdesc
                                                    and xf.location = g.location
                                                    and g.eventtime >= trunc(to_timestamp(:begindate,'yyyy-mm-dd hh24:mi:ss.ff'))
                                                    and g.eventtime <= trunc(to_timestamp(:enddate,'yyyy-mm-dd hh24:mi:ss.ff'))+1
                                                    and to_char(g.eventtime,'yyyy-mm-dd') = to_char(xf.finishdate,'yyyy-mm-dd')
                                                        )
                                        ` :
                                        `
                                        and not exists (
                                                select 1 from c##wxw.crt_list r
                                                where xf.sbdesc = r.devicenum
                                                --and xf.location = r.location
                                                and r.eventtime >= trunc(to_timestamp(:begindate,'yyyy-mm-dd hh24:mi:ss.ff'))
                                                and r.eventtime <= trunc(to_timestamp(:enddate,'yyyy-mm-dd hh24:mi:ss.ff'))+1
                                                and to_char(r.eventtime,'yyyy-mm-dd') = to_char(xf.finishdate,'yyyy-mm-dd')
                                                        )
                                        `
        
        colName = ""
        checkDatas.push(begindate)
        checkDatas.push(enddate)
        tableJoin = ""
        eventtimeOrder = ""
    } else { 
        noexistsWhere = ""         
        colName = line === 'L03' ? `,g.EVENTTIME
                                    ,g.ALARM_REGION
                                    ,g.SBDESC as gsbd
                                    ,g.STATUS`
                                : `,r.eventtime
                                   ,r.devicenum as rdev
                                   ,r.devdesc
                                   ,r.description as rd
                                   ,r.status                                     
                                    `
        tableJoin = line === 'L03' ? `
                                    left join C##WXW.GCC_LIST g
                                    on xf.sbdesc = g.sbdesc
                                    and xf.location = g.location
                                    and g.eventtime >= trunc(to_timestamp(:begindate, 'yyyy-mm-dd hh24:mi:ss.ff'))
                                    and g.eventtime <= trunc(to_timestamp(:enddate, 'yyyy-mm-dd hh24:mi:ss.ff')) + 1 
                                    and to_char(g.eventtime, 'yyyy-mm-dd') = to_char(xf.finishdate, 'yyyy-mm-dd')
                                    ` 
                                    : `
                                    left join c##wxw.crt_list r
                                    on xf.sbdesc = r.devicenum
                                    and r.eventtime >= to_timestamp(:begindate, 'yyyy-mm-dd hh24:mi:ss.ff')
                                    and r.eventtime <= trunc(to_timestamp(:enddate, 'yyyy-mm-dd hh24:mi:ss.ff')) + 1 
                                    and to_char(r.eventtime, 'yyyy-mm-dd') = to_char(xf.finishdate, 'yyyy-mm-dd')
                                    `
        eventtimeOrder = line === 'L03' ? `,g.eventtime` : `,r.eventtime`
        checkDatas.push(begindate)
        checkDatas.push(enddate)
    }
    

    //查询SQL
    let checkSql = `
        with xf as(
            select x.wonum,x.description as xd
                ,x.area,x.ispass,x.FINISHDATE
                ,x.PERSON,x.REMARK
                ,x.CUST_LINENUM,x.SPEC_DESC,x.SUB_DESC	
                ,x.PGNAME,to_char(x.SCHEDFINISH,'yyyy') as year
                ,x.AREA2,x.EXCELSEQ
                ,case when x.location = 'L03F01' then 'L03Z11'
                    when x.location = 'L03F02' then 'L03Z11'
                    when x.location = 'L03F03' then 'L03Z12'
                    when x.location = 'L03F04' then 'L03Z13'
                    when x.location = 'L03F05' then 'L03Z10'
                    when x.location = 'L03F06' then 'L03Z07'
                    when x.location = 'L3BF01' then 'L3BZ27'
                    when x.location = 'L3BF03' then 'L3BZ23'
                    when x.location = 'L3BF04' then 'L3BZ23'
                    when x.location = 'L3BF05' then 'L3BZ22'
                    when x.location = 'L3BF06' then 'L3BZ21'
                    when x.location = 'L3BF07' then 'L3BZ20'
                    when x.location = 'L3BB02' then 'L3BZ30'
                    else x.location
                    end as location
                ,x.sbdesc,x.devicenum
            from C##WXW.XFJIANCE x
            where x.cust_linenum = :line         
            and x.finishdate >= trunc(to_timestamp(:begindate,'yyyy-mm-dd hh24:mi:ss.ff'))
            and x.finishdate <= trunc(to_timestamp(:enddate,'yyyy-mm-dd hh24:mi:ss.ff'))+1
            and (x.cust_subsys = 'FASFGW'
                or x.cust_subsys = 'FASFSB'
                or x.cust_subsys = 'FASFYG'
                or (x.cust_subsys = 'FASMOS' and x.area <> 'IBP')
                or x.cust_subsys = 'GQTSFD'
                or x.cust_subsys = 'FASQSB'
                or x.cust_subsys = 'FASQXF'
                or x.cust_subsys = 'FASXFS'	 
                or x.cust_subsys = 'FASFHH'	
                or x.cust_subsys = 'FASKRQ'	      
                ) 
            ${stationsWhere}
            ${specialtiesWhere}
            ${subsysWhere}
            ${descWhere}
            and x.sbdesc is not null
        )
        ----------------------------------------------------------------------------------------------
        select xf.wonum,xf.xd
            ,xf.area,xf.AREA2,xf.ispass
            ,xf.FINISHDATE,xf.PERSON,xf.REMARK
            ,xf.CUST_LINENUM,l.description as sta
            ,xf.SPEC_DESC,xf.SUB_DESC	
            ,xf.PGNAME,xf.year
            ,xf.devicenum,xf.sbdesc
            ${colName}        
        from  xf
        inner join C##WXW.LOCATION l
        on xf.location = l.location
        ${tableJoin}
        ${noexistsWhere}
        where 1 = 1	
        order by xf.devicenum,xf.FINISHDATE${eventtimeOrder}
        `
    // console.log(checkSql)
    // console.log(checkDatas)
    // console.log(typeof noexists)

    try{
        let fasRecordCheckInfo = await db.search(checkSql,checkDatas)
        resDatas = {fasRecordCheckInfo}
        msg = resDatas.fasRecordCheckInfo.errorNum ? "查询失败" : "查询成功"
        resStatus = resDatas.fasRecordCheckInfo.errorNum ? 0 : 1
        res.json({
            status:resStatus,
            msg,
            resDatas 
        })
    }catch(err){
        res.json({
            status:0,
            msg:"查询失败",
            resDatas:{err}
        })
    }
}